import xlrd
import os
import openpyxl
import xlwt

def read_write_xlsx(dirname, fileExtension,sheetname):

    result = []
    for item in os.listdir(dirname):
        item_path = os.path.join(dirname, item)
        if os.path.isdir(item_path):
            search(item_path, fileExtension)
        elif os.path.isfile(item_path):
            if fileExtension in item:
                result.append('.'+item_path.split('read_write_Excel')[1])
                print('.'+item_path.split('read_write_Excel')[1])

    data_list = []
    for fileExtension in result:
        bk = xlrd.open_workbook(fileExtension)
        shxrange = range(bk.nsheets)
        try:
            sh = bk.sheet_by_name("Sheet1")
        except:
            print("no sheet in %s named Sheet1" % fname)
        # 获取行数
        nrows = sh.nrows
        # 获取列数
        ncols = sh.ncols
        #print("nrows %d, ncols %d" % (nrows, ncols))
        # 获取第一行第一列数据
        cell_value = sh.cell_value(1, 1)
        # print cell_value

        row_list = []
        # 获取各行数据
        for i in range(1, nrows):
            row_data = sh.row_values(i)
            row_list.append(row_data)

        print(row_list)


        new_row_list = []
        new_row_list.append(row_list[1][1].split('：')[1])
        new_row_list.append(row_list[1][3].split('：')[1])

        data_list.append(new_row_list)

    print(data_list)


    wb = openpyxl.Workbook(r'/Users/zhehe/PycharmProjects/testBridge/read_write_Excel/data.xls')
    wb.create_sheet(sheetname)
    sheet = wb.active
    titles = ['桥梁名称', '桥梁所在地']
    sheet.append(titles)

    for index, p in enumerate(data_list):
        sheet.row_dimensions[index + 2].height = 28.0
        sheet.append(p)
    wb.save(r'/Users/zhehe/PycharmProjects/testBridge/read_write_Excel/data.xls')

if __name__ == '__main__':

    read_write_xlsx(r'/Users/zhehe/PycharmProjects/testBridge/read_write_Excel/excel_data', ".xls", 'sheet1')
